package mysql

import (
	_ "github.com/go-sql-driver/mysql"

	"database/sql"
	"fmt"
	"strings"
)

type Entry struct {
	Name     string
	WorkID   string
	DeviceID string
	FilePath string
	FileHash string
}

const (
	USERNAME  = "root"
	PASSWORD  = "123456"
	IP        = "10.10.9.35"
	PORT      = "3306"
	dbName    = "mysql"
	TableName = "file_table"
)

type Database struct {
	DB *sql.DB
}

func (db *Database) InitDB() error {
	var err error
	path := strings.Join([]string{USERNAME, ":", PASSWORD, "@tcp(", IP, ":", PORT, ")/", dbName, "?charset=utf8"}, "")
	db.DB, err = sql.Open("mysql", path)
	if err != nil {
		return err
	}
	db.DB.SetConnMaxLifetime(100)
	db.DB.SetMaxIdleConns(10)
	if err := db.DB.Ping(); err != nil {
		return fmt.Errorf("open database fail")
	}
	return nil
}

func (db *Database) DropTable(table string) error {
	_, err := db.DB.Exec("DROP TABLE IF EXISTS " + table)
	if err != nil {
		return fmt.Errorf("drop table error: %v", err)
	}
	return nil
}

func (db *Database) CreateTable(table string) error {
	_, err := db.DB.Exec("CREATE TABLE IF NOT EXISTS " + table + " (name VARCHAR(32) NOT NULL, workID VARCHAR(16) NOT NULL, deviceID VARCHAR(16) NOT NULL, filePath VARCHAR(64) NOT NULL, fileHash VARCHAR(64) NOT NULL)")
	if err != nil {
		return fmt.Errorf("create table error: %v", err)
	}
	return nil
}

func (db *Database) InsertEntry(entry Entry, table string) error {
	_, err := db.DB.Exec("INSERT INTO " + table + " (`name`, `workID`, `deviceID`, `filePath`, `fileHash`) VALUES ('" + entry.Name + "','" + entry.WorkID + "','" + entry.DeviceID + "','" + entry.FilePath + "','" + entry.FileHash + "')")
	if err != nil {
		return fmt.Errorf("insert entry error: %v", err)
	}
	// // 开启事务
	// tx, err := db.DB.Begin()
	// if err != nil {
	// 	return fmt.Errorf("tx begin error: %v", err)
	// }
	// // 准备sql语句
	// stmt, err := tx.Prepare("INSERT INTO " + table + " (`name`, `workID`, `deviceID`, `filePath`, `fileHash`) VALUES (?, ?, ?, ?, ?)")
	// if err != nil {
	// 	return fmt.Errorf("tx prepare error: %v", err)
	// }
	// // 执行sql语句
	// _, err = stmt.Exec(entry.Name, entry.WorkID, entry.DeviceID, entry.FilePath, entry.FileHash)
	// if err != nil {
	// 	return fmt.Errorf("tx exec error: %v", err)
	// }
	// // 事务提交
	// tx.Commit()
	// 获得上一个插入自增的id
	// fmt.Println(res.LastInsertId())
	return nil
}

func (db *Database) SelectEntry(table string) error {
	_, err := db.DB.Exec("SELECT `fileHash` FROM " + table + "where `name`='Zhang San'")
	if err != nil {
		return fmt.Errorf("select entry error: %v", err)
	}
	return nil
}

func (db *Database) InsertEntrys(entrys []Entry, table string) error {
	// 开启事务
	tx, err := db.DB.Begin()
	if err != nil {
		return fmt.Errorf("tx begin error: %v", err)
	}
	// 准备sql语句
	stmt, err := tx.Prepare("INSERT INTO " + table + " (`name`, `workID`, `deviceID`, `filePath`, `fileHash`) VALUES (?, ?, ?, ?, ?)")
	if err != nil {
		return fmt.Errorf("tx prepare error: %v", err)
	}
	// 执行sql语句
	for _, entry := range entrys {
		_, err := stmt.Exec(entry.Name, entry.WorkID, entry.DeviceID, entry.FilePath, entry.FileHash)
		if err != nil {
			return fmt.Errorf("tx exec error: %v", err)
		}
	}
	// 事务提交
	tx.Commit()
	return nil
}

func (db *Database) Close() {
	db.DB.Close()
}
